"""Initial migration

Revision ID: initial_migration
Revises: 
Create Date: 2024-03-21

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = 'initial_migration'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    # 创建角色表
    op.create_table('role',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(64), unique=True),
        sa.Column('description', sa.String(255)),
        sa.Column('permissions', sa.Integer()),
        sa.PrimaryKeyConstraint('id')
    )

    # 创建用户表
    op.create_table('user',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('username', sa.String(64), unique=True),
        sa.Column('email', sa.String(120), unique=True),
        sa.Column('password_hash', sa.String(128)),
        sa.Column('created_at', sa.DateTime(), default=sa.func.current_timestamp()),
        sa.Column('last_login', sa.DateTime()),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('session_token', sa.String(100), unique=True),
        sa.PrimaryKeyConstraint('id')
    )

    # 创建用户角色关联表
    op.create_table('user_roles',
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('user.id')),
        sa.Column('role_id', sa.Integer(), sa.ForeignKey('role.id')),
        sa.PrimaryKeyConstraint('user_id', 'role_id')
    )

    # 创建论文表
    op.create_table('thesis',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('title', sa.String(200), nullable=False),
        sa.Column('description', sa.Text()),
        sa.Column('programming_language', sa.String(100), nullable=False),
        sa.Column('price', sa.Float(), nullable=False),
        sa.Column('status', sa.String(20), server_default='pending'),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.current_timestamp()),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('user.id'), nullable=False),
        sa.Column('contact_info', sa.String(100), nullable=False),
        sa.Column('deadline', sa.DateTime(), nullable=False),
        sa.Column('need_environment', sa.Boolean(), server_default='0'),
        sa.Column('proposal_file', sa.String(255)),
        sa.Column('thesis_type', sa.String(20), nullable=False, server_default='design'),
        sa.PrimaryKeyConstraint('id')
    )

    # 创建消息表
    op.create_table('message',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('content', sa.Text(), nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.current_timestamp()),
        sa.Column('is_read', sa.Boolean(), server_default='0'),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('user.id'), nullable=False),
        sa.Column('thesis_id', sa.Integer(), sa.ForeignKey('thesis.id'), nullable=False),
        sa.PrimaryKeyConstraint('id')
    )

    # 创建索引
    op.create_index('idx_user_created', 'thesis', ['user_id', 'created_at'])

def downgrade():
    op.drop_index('idx_user_created', 'thesis')
    op.drop_table('message')
    op.drop_table('thesis')
    op.drop_table('user_roles')
    op.drop_table('user')
    op.drop_table('role') 